Application
This unit describes the skills and knowledge required to produce structured query language (SQL) statements to work with server-side scripts, enabling web designers to interact with web server databases.
It applies to individuals who are employed as web designers responsible for creating server-side interaction with dynamic web pages, using SQL as a means of communicating with the database.
No licensing, legislative or certification requirements apply to this unit at the time of publication.
Elements and Performance Criteria
ELEMENT | PERFORMANCE CRITERIA |
Elements describe the essential outcomes. | Performance criteria describe the performance needed to demonstrate achievement of the element. |
1. Identify SQL requirements from the specification documentation | 1.1 Determine the information to be used from the database 1.2 Identify the tables that hold this information 1.3 Identify the primary keys in these tables 1.4 Identify the relationships between these tables, including foreign keys |
2. Create a relational database | 2.1 Create tables in a database, using SQL statements 2.2 Identify the primary and foreign keys for database table 2.3 Manipulate the data in a database, using SQL statements 2.4 Query the database, using SQL statements 2.5 Write SQL statements to retrieve information from the database |
3. Test SQL results | 3.1 Construct test data to test SQL statements 3.2 Determine the expected results in order to verify SQL statements 3.3 Verify the result of the constructed SQL statements, against the expected results |
Evidence of Performance
Evidence of the ability to:
identify structured query language (SQL) requirements
create the databases and tables
generate queries for one or more tables, to provide the required data
add, modify and delete records from the database tables
test and verify SQL statements.
Note: If a specific volume or frequency is not stated, then evidence must be provided at least once.
Evidence of Knowledge
To complete the unit requirements safely and effectively, the individual must:
explain the features and application of the following aggregate functions:
MIN
MAX
SUM
AVG
COUNT
COUNT(*)
explain the features and application of the following clause functions:
GROUP BY
HAVING
ORDER BY
dates and times
SQL data types
numbers
text
SQL syntax:
SELECT
FROM
WHERE
LIKE
DISTINCT
CREATE
ALTER TABLE
INSERT INTO
UPDATE
DELETE
DROP
describe the principles of "combining and/or condition" in SQL statements and Boolean operators:
IN and BETWEEN conditional operators
mathematical operators
table joins (relationships).
Assessment Conditions
Gather evidence to demonstrate consistent performance in conditions that are safe and replicate the workplace. Noise levels, production flow, interruptions and time variances must be typical of those experienced in the website technologies field of work, and include access to:
a development environment
a database server.
Assessors must satisfy NVR/AQTF assessor requirements.
Foundation Skills
This section describes language, literacy, numeracy and employment skills incorporated in the performance criteria that are required for competent performance.
Skill | Performance Criteria | Description |
Reading | 1.1-1.4, 2.3, 3.3 | Analyses and consolidates information and data from a range of sources, against the defined criteria and requirements, and checks for accuracy and completeness |
Writing | 2.1, 2.3-2.5, 3.1, 3.2 | Uses clear and specialised technical syntax when creating codes and interrogating databases Prepares and produces, diagrammatic models and associated documents, that that convey complex relationships between data |
Get the work done | 1.1, 1.4, 2.2-2.5, 3.2, 3.3 | Takes responsibility for planning, sequencing and prioritising tasks and own workload, for efficiency and effective outcomes Makes routine decisions and implements standard procedures for routine tasks, using formal decision-making processes for more complex and non-routine situations Addresses less predictable problems and initiates standard procedures in response to these problems, applying problem-solving processes in determining a solution Uses familiar digital technologies and systems to access information, search and enter, data and code, present information, and communicate with others, cognisant of data security and safety |
Sectors
Web